/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.FileUtils;
import utils.Handle;

/**
 *
 * @author jamie
 */
public class MakeProbeIdLookupTable {

    private File lookupFile = new File("/Users/jamie/monkeys/microarrays/probeIds/lookup.txt");

    public void makeLookupTable() {
        Handle handle = Handle.getConnectedHandle("monkeys");
        String sql = "drop table if exists probeIdLookup";
        handle.runSQLupdate(sql);
        sql = "create table probeIdLookup (probeId int(10) not null, gId int(10) not null)";
        handle.runSQLupdate(sql);
        sql = "create index probeIdLookup_index on probeIdLookup(probeId)";
        handle.runSQLupdate(sql);
        sql = "create index gIdLookup_index on probeIdLookup(gId)";
        handle.runSQLupdate(sql);
        handle.closeConnection();
    }

    public void addData() {
        BufferedReader reader = FileUtils.getBufferedReader(lookupFile);
        String line = null;
        Handle handle = Handle.getConnectedHandle("monkeys");
        boolean first = true;
        try {
            while ((line = reader.readLine()) != null) {
                if (first) {
                    first = false;
                } else {
                    String split[] = line.split("\t");
                    int probeId = Integer.parseInt(split[0]);
                    int[] gIds = getGeneIds(split[1]);
                    for (int gId : gIds) {
                        String sql = "insert into probeIdLookup values(" + probeId + ", " + gId + ")";
                        handle.runSQLupdate(sql);
                    }
                }
            }
            reader.close();
        } catch (IOException ex) {
            Logger.getLogger(MakeProbeIdLookupTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        handle.closeConnection();
    }

    private int[] getGeneIds(String string) {
        int[] arr;
        if (string.contains("|")) {
            String split[] = string.split("\\|");
            arr = new int[split.length];
            int i = 0;
            for (String s : split) {

                arr[i] = Integer.parseInt(s);
                i++;
            }
        } else {
            arr = new int[1];
            try{
                arr[0] = Integer.parseInt(string);
            } catch(NumberFormatException exp){
                arr[0] = -1;
            }
        }
        return arr;
    }
}
